/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.*;
import java.util.ArrayList;
import DTO.DanhMuc_DTO;
/**
 *
 * @author phihai
 */
public class DanhMuc_DAO {
    
    Database db = new Database();
    Connection conn = null;
    PreparedStatement pst = null;
    
    public ArrayList<DanhMuc_DTO> DSRD_DanhMuc() throws Exception
    {
        ArrayList<DanhMuc_DTO> arr = new ArrayList<DanhMuc_DTO>(); 
        String sql = "SELECT Top(6)* FROM DanhMuc order by NEWID()";       
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            DanhMuc_DTO dt = new DanhMuc_DTO();   
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            dt.setTenDanhMuc(rs.getString("TenDanhMuc"));          
            
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public ArrayList<DanhMuc_DTO> DS_DanhMuc() throws Exception
    {
        ArrayList<DanhMuc_DTO> arr = new ArrayList<DanhMuc_DTO>(); 
        String sql = "SELECT * FROM DanhMuc";       
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            DanhMuc_DTO dt = new DanhMuc_DTO();   
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            dt.setTenDanhMuc(rs.getString("TenDanhMuc"));         
            
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public DanhMuc_DTO LaySPTheoID(int ID) throws Exception
    {
        ArrayList<DanhMuc_DTO> arr = new ArrayList<DanhMuc_DTO>(); 
        String sql = "SELECT * FROM DanhMuc WHERE IDDanhMuc ="+ID ;  
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        DanhMuc_DTO dt = new DanhMuc_DTO(); 
        while (rs.next())
        {              
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            dt.setTenDanhMuc(rs.getString("TenDanhMuc"));           
            
            
        }
        db.close();
        return dt;
    }
    
    public ArrayList<DanhMuc_DTO>TimSPTheoTen(String tenSP) throws Exception
    {
        ArrayList<DanhMuc_DTO> arr = new ArrayList<DanhMuc_DTO>(); 
        String sql = "SELECT * FROM DanhMuc WHERE TenDanhMuc LIKE '" + tenSP +"%'" ;  
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            DanhMuc_DTO dt = new DanhMuc_DTO();   
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            dt.setTenDanhMuc(rs.getString("TenDanhMuc"));           
           
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public void ThemDanhMuc(DanhMuc_DTO sp) throws Exception
    {
         String sql = "INSERT INTO DanhMuc (TenDanhMuc) VALUES (?)";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
               
         pst.setString(1, sp.getTenDanhMuc());         
         
        
         pst.executeUpdate();
         
         db.close();
    }
    
     public void CapNhatDanhMuc(DanhMuc_DTO sp) throws Exception
    {
         String sql = "UPDATE DanhMuc SET TenDanhMuc=? WHERE IDDanhMuc=?";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
               
         pst.setString(1, sp.getTenDanhMuc());         
         pst.setInt(2, sp.getIDDanhMuc());
        
         pst.executeUpdate();
         
         db.close();
    }
    
    public int XoaDanhMuc(int id_sp) throws Exception
    {
         String sql = "DELETE DanhMuc WHERE IDDanhMuc=?";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
         
         pst.setInt(1, id_sp);
         
         int kq = pst.executeUpdate();
         
         db.close();
         return kq;
    }
}
